import sqlite3,time,uuid
connect=sqlite3.connect('p2p.db')
cursor=connect.cursor()
# cursor.execute('''create table friends
#                (
#                id text primary key,
#                name text not null,
#                ip text not null,
#                device_info not null,
#                online int not null
#                )
#                ''')

# members TEXT,   -- 存储为 JSON 字符串,[{"username":"","id":"",ip":"","role":"","online":""},{}],"role"这个字段为空则为群普通成员，为owner则为群主
# creator TEXT,   -- 群主 DID（冗余存储便于查询）
# cursor.execute('''create table tblGroup
#                (
#                 groupId char(36) not null,
#                 groupname text not null,
#                 members text, 
#                 timestamp INTEGER NOT NULL,
#                 owner text not null
#                )                    
# ''')
# cursor.execute("delete from friend_requests")
# cursor.execute("delete from friend_requests")# cursor.execute('''drop table tblGroup''')
# 此处的message={"content":,
#              "timestamp":"发消息的那个人发送过来的时间",
# #              "msg_type":"text" }
# cursor.execute('''
#  CREATE TABLE IF NOT EXISTS group_message (
#     sender TEXT NOT NULL,
#     sender_name TEXT NOT NULL,
#     receiver TEXT DEFAULT 'All', 
#     message TEXT NOT NULL,
#     groupId CHAR(36) NOT NULL,
#     owner TEXT NOT NULL
# )
# ''')
# cursor.execute('''drop table group_message''')
# #cursor.execute("delete from current_user")
# cursor.execute("delete from user")
# cursor.execute('''
# CREATE TABLE user (
#     id TEXT PRIMARY KEY,  -- 使用自定义的唯一 ID,而不是自增
#     name TEXT NOT NULL UNIQUE,
#     password TEXT NOT NULL,
#     ip TEXT  not null,
#     info text ,
#     host int            
# );
#                ''')
# cursor.execute('''drop table user ''')
# cursor.execute(''' alter table friends add column owner text''')
# create_pendingfriends='''CREATE TABLE IF NOT EXISTS messages (
#     id INTEGER PRIMARY KEY AUTOINCREMENT,
#     friend_id TEXT,
#     sent text, -- 1=我发的，0=对方发的
#     message TEXT,
#     timestamp INTEGER,
#     is_sent INTEGER  -- 1 表示已发送，0 表示待发送
# );'''
# cursor.execute("drop table friend_requests")
# cursor.execute(create_pendingfriends)
# cursor.execute('''
# CREATE TABLE friends (
#     id TEXT PRIMARY KEY,
#     name TEXT NOT NULL,
#     ip TEXT NOT NULL,
#     device_info TEXT NOT NULL,
#     online INTEGER NOT NULL
# )
# ''')
# cursor.execute("alter table friends  add column timestamp INTEGER ")
# cursor.execute(create_pendingfriends)          
# cursor.execute('''
# CREATE TABLE current_user (
#     id text not null,
#     name TEXT NOT NULL,
#     ip TEXT NOT NULL
# );
# '''
# # )
# cursor.execute("alter table group_message  add column owner text not null ")
# cursor.execute("drop table friend_requests")
# #'pending_send' 代表“发送给别人但是还没有发送出去同意的申请”
# # 'pending_accept' 代表“收到但是未接受的好友申请”
# # 'pending_agree' 代表“未发出的好友同意”
# cursor.execute('''
#  CREATE TABLE friend_requests (

#     sender_id TEXT NOT NULL,
#     sender_name TEXT NOT NULL,
#     sender_ip TEXT NOT NULL,
#     sender_timestamp INTEGER NOT NULL,
    
#     receiver_id TEXT NOT NULL,
#     receiver_name TEXT NOT NULL,
#     receiver_ip TEXT NOT NULL,
#     receiver_timestamp INTEGER NOT NULL,
    
#     status TEXT NOT NULL  -- 状态字段，比如 'pending_send', 'pending_accept', 'pending_agree'
# );
# ''')
# cursor.execute("ALTER TABLE friend_requests ADD COLUMN owner TEXT")
# cursor.execute("ALTER TABLE friends ADD COLUMN timestamp INTEGER")

# cursor.execute("ALTER TABLE friends ADD COLUMN host_id TEXT DEFAULT 0")
# cursor.execute("ALTER TABLE current_user ADD COLUMN host INTEGER ")
# cursor.execute('DROP TABLE friends')
# cursor.execute('''
#     CREATE TABLE IF NOT EXISTS friends(
#         id TEXT PRIMARY KEY,
#         name TEXT NOT NULL,
#         ip TEXT NOT NULL,
#         online INTEGER NOT NULL,
#         host_id TEXT DEFAULT 0
#     )
# ''')
# cursor.execute("PRAGMA table_info(messages)")  # 替换为你想查看的表名
# columns = cursor.fetchall()

# print("字段信息：")
# for column in columns:
#     print(f"序号: {column[0]}, 列名: {column[1]}, 类型: {column[2]}, 是否主键: {column[5]}")
# cursor.execute("delete from friend_requests")
# cursor.execute("ALTER TABLE friends ADD COLUMN owner TEXT")
# cursor.execute("insert into friends values(?,?,?,?,?,?,?)",("did:p2p_did_chat:l3i7nMgRYu","testuser2","192.168.119.84",1,0,1753516815693,'did:p2p_did_chat:Pf5WlFjC09'))
# cursor.execute("""
# CREATE TABLE IF NOT EXISTS pending_friends (
#     id TEXT PRIMARY KEY,
#     name TEXT,
#     ip TEXT,
#     timestamp INTEGER,
#     online INTEGER DEFAULT 0
# )
# """)
# import time
# timestamp_now = int(time.time() * 1000)
# cursor.execute('''
#     INSERT INTO friend_requests(
#         sender_id, sender_name, sender_ip, sender_timestamp,
#         receiver_id, receiver_name, receiver_ip, receiver_timestamp,
#         status, owner
#     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
# ''', (
#     "did:p2p_did_chat:GRkauOstly",
#     "xxx",
#     "192.168.9.84",
#     timestamp_now,
#     "did:p2p_did_chat:C5YdYOFY35",
#     "yyy",
#     "192.168.9.99",
#     timestamp_now,
#     "pending_accept",
#     "did:p2p_did_chat:C5YdYOFY35"
# ))
# cursor.execute("delete from messages where message='0'")
# cursor.execute("delete from friend_requests")
# cursor.executescript("""
# INSERT OR REPLACE INTO pending_friends (id, name, ip, timestamp, online) VALUES
# ('001', 'Alice', '192.168.11.1', 1753250000000, 0),
# ('002', 'Bob', '192.168.11.2', 1753250100000, 1),
# ('003', 'Charlie', '192.168.11.3', 1753250200000, 0);
# """)
# owner = 'did:p2p_did_chat:qSnSZNrtpE'
# receiver_name = "CurrentUser"
# receiver_ip = "192.168.11.100"

# base_timestamp = 1753260000000

# test_data = {
#         "004": "David",
#         "005": "Eva",
#         "006": "Frank"
#     }

# for i, (sender_id, sender_name) in enumerate(test_data.items()):
#         sender_ip = f"192.168.11.{int(sender_id)}"
#         timestamp = base_timestamp + i
#         cursor.execute("""
#             INSERT OR REPLACE INTO friend_requests (
#                 sender_id, sender_name, sender_ip, sender_timestamp,
#                 receiver_id, receiver_name, receiver_ip, receiver_timestamp,
#                 status, owner
#             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
#         """, (
#             sender_id,
#             sender_name,
#             sender_ip,
#             timestamp,
#             owner,
#             receiver_name,
#             receiver_ip,
#             timestamp,
#             "pending_accept",
#             owner
#         ))
# cursor.execute("INSERT INTO friends VALUES ('did:p2p_did_chat:l3i7nMgRYu', 'testuser2', '10.69.178.86', 1, 0, 1753260000002)")
# cursor.execute("UPDATE friends SET online = 1")
# cursor.execute("UPDATE friends SET online = ?", (1,))
# cursor.execute("drop table device")
# cursor.execute('''
# CREATE TABLE IF NOT EXISTS device (
#     id TEXT PRIMARY KEY,             -- 唯一设备 DID（如 did:p2p_device:XXX）
#     name TEXT NOT NULL,              -- 设备名称（如 “打印机 HP-2520”）
#     type TEXT NOT NULL,              -- 设备类型（如 printer, camera）
#     owner_id TEXT,                   -- 所属用户 ID（可关联 user 表）
#     host TEXT,                       -- 设备所在主机标识（如 IP 或主机名）
#     status INTEGER DEFAULT 0,        -- 状态（0=离线，1=在线）
#     timestamp INTEGER              -- 添加时间戳
# );

# ''')
# cursor.execute("delete from device")
cursor.execute("drop table group_message")
cursor.execute('''
CREATE TABLE IF NOT EXISTS group_message (
    sender TEXT NOT NULL,
    sender_name TEXT NOT NULL,
    receiver TEXT DEFAULT 'All', 
    message TEXT NOT NULL,
    groupId CHAR(36) NOT NULL,
    owner TEXT NOT NULL
)
''')
connect.commit()
connect.close()
# import sqlite3

# conn = sqlite3.connect("p2p.db")
# cursor = conn.cursor()

# # 第 1 步：重命名原表
# cursor.execute("ALTER TABLE friends RENAME TO friends_old")
# # cursor.execute("drop table friends")
# # 2. 创建新表，设置联合主键 (id, owner)
# cursor.execute("""
#     CREATE TABLE friends (
#         id TEXT,
#         name TEXT NOT NULL,
#         ip TEXT NOT NULL,
#         online INTEGER NOT NULL,
#         host_id TEXT DEFAULT 0,
#         timestamp INTEGER NOT NULL,
#         owner TEXT,
#         PRIMARY KEY (id, owner)
#     )
# """)

# # 3. 将旧数据迁移到新表
# cursor.execute("""
#     INSERT OR IGNORE INTO friends (id, name, ip, online, host_id, owner)
#     SELECT id, name, ip, online, host_id, owner FROM friends_old
# """)

# # 4. 删除旧表
# cursor.execute("DROP TABLE friends_old")

# conn.commit()
# conn.close()

# print("✅ 表结构修改成功，数据已保留，联合主键 (id, owner) 生效")
# import sqlite3

# def print_table_info(db_path, table_name):
#     conn = sqlite3.connect(db_path)
#     cursor = conn.cursor()

#     cursor.execute(f"PRAGMA table_info({table_name})")
#     columns = cursor.fetchall()

#     if not columns:
#         print(f"[!] 表 '{table_name}' 不存在或没有字段。")
#         return

#     print(f"表 `{table_name}` 的字段信息：")
#     print("{:<5} {:<15} {:<15} {:<10} {:<15} {:<5}".format("cid", "name", "type", "notnull", "dflt_value", "pk"))

#     for col in columns:
#         # 替换 None 为字符串 "NULL"
#         col = [str(item) if item is not None else "NULL" for item in col]
#         print("{:<5} {:<15} {:<15} {:<10} {:<15} {:<5}".format(*col))

#     conn.close()

# # 使用示例
# print_table_info("p2p.db", "user")

# # 使用示例
# # print_table_info("p2p.db", "user")
# def insert_sample_device():
#     device = {
#         'type': 'printer',
#         'name': 'Microsoft Print to PDF',
#         'status': True
#     }
#     # 固定值
#     owner_id = 'did:p2p_did_chat:p4XrPr8qaD'
#     host = '127.0.0.1'
#     timestamp = int(time.time() * 1000)
#     status = 1 if device['status'] else 0

#     # 生成不带 did: 前缀的设备 ID（你可以自定义规则）
#     device_id = f"device_{uuid.uuid4().hex[:16]}"

#     conn = sqlite3.connect("p2p.db")
#     cursor = conn.cursor()

#     cursor.execute('''
#         INSERT INTO device (id, name, type, owner_id, host, status, timestamp)
#         VALUES (?, ?, ?, ?, ?, ?, ?)
#     ''', (device_id, device['name'], device['type'], owner_id, host, status, timestamp))

#     conn.commit()
#     conn.close()
#     print(f"✅ 已插入设备：{device_id} - {device['name']}")

# if __name__ == '__main__':
#     # init_db()
#     insert_sample_device()
